from typing import Union
import pandas as pd
from  datetime import datetime,date,time,timedelta
from sqlalchemy import create_engine

host = "39.101.184.255"
port = "3306"
user = "root"
pwd = "hw4865297"

class MysqlConn:

    def __init__(self):
        print("init success!")

    def insert_dataframe(self, df: pd.DataFrame, db: str, collection: str):
        conn = create_engine(f'mysql+pymysql://{user}:{pwd}@{host}:{port}/{db}?charset=utf8')
        df.to_sql(name=collection,con=conn,schema=db,if_exists='append',index=False)

    def read_dataframe(self, db: str, collection: str, filter: Union[dict, None] = None):
        conn = create_engine(f'mysql+pymysql://{user}:{pwd}@{host}:{port}/{db}?charset=utf8')
        if filter is not None:
            f_sql = "1=1"
            for key in filter:
                f_sql += " and " + key + " "+filter[key]
            sql = f"select distinct * from {db}.{collection} where {f_sql}"
            # print(sql)
            return pd.read_sql(sql=f"select distinct * from {db}.{collection} where {f_sql}",con=conn)
        else:
            return pd.read_sql(sql=f"select distinct * from {db}.{collection} where 1=1",con=conn)

    # def get_md_data_from_clickhouse(self, db: str, collection: str,symbol: str,start_date:datetime,end_date:datetime):
    #     return self.client.execute(f"select * from {db}.{collection} where 1=1 and symbol = '{symbol}' and trading_date >= '{datetime.datetime.strptime(start_date,'%Y-%m-%d %H:%M:%S')}' and trading_date <= '{datetime.datetime.strptime(end_date,'%Y-%m-%d %H:%M:%S')}'")

    # def execute_sql(self,sql:str):
    #     return self.client.execute(sql)

